import getpath
import os
import xlrd
import re
import logging
import copy
def elementWave(s):
    # find = re.search(r"\d+(\.\d+)?  [A-Z][a-z]*", s).group()
    if "/" in s:
        (element,wave)=s.split("/")
        return (element,wave)
    if "-" in s:
        (element,wave)=s.split("-")
        return(element,wave)
    element=s
    return(element,"")
def getdata_raw(fname):
    from openpyxl import load_workbook
    xl_Book = load_workbook(filename=fname)
    table = xl_Book.get_sheet_by_name("Sheet1")
    data = []
    for row in table.values:
        r = []
        for one in row:
            if one is not None:
                r.append(one)
            else:
                r.append("")
        data.append(r)
    return data    
def zl_data(data):
    start=1
    data_ele=data[0][start:]
    at=0
    items={}
    while(at<len(data_ele)):
        r=elementWave(data_ele[at])
        items[r]=at+start
        at+=1
    data=data[1:]
    r=[]
    for i in range(len(data)):
        sample={}
        sample["items"]=copy.copy(items)
        row=data[i]
        if len(row)<2:
            break
        id1=row[0]
        # type1=row[4]
        # conc=row[8]
        if id1=="":
            continue
        sample["id"]=str(id1)
        # sample["xishi"]=row[7]
        for item in sample["items"].keys():
            print(item)
            print(sample["items"][item])
            sample["items"][item]=row[sample["items"][item]]
        # sample["nb"]=nb
        # sample["conc"]=[conc]
        r.append(sample)
    return r
def getdata(fname):
    data0=getdata_raw(fname)
    data=zl_data(data0)
    return data
def test_getdata():
    fname=os.path.join(getpath.getpath(),"stds.xlsx")
    data=getdata(fname)
    # print(data)
    return data
if __name__=="__main__":
    data=test_getdata()
    print(data)
    for one in data:
        print(one["id"])
    print(data[0])
    print(data[1])
    print(data[-2])
    print(data[-1])
# from openpyxl import load_workbook
# def importdata():
#     xlBook = load_workbook(filename=filename)
#     table = xlBook.get_sheet_by_name("Sheet1")
#     table = xlBook.worksheets[0]
#     logging.info(dir(table))
#     create_table = False
#     data = []
#     for row in table.values:
#         r = []
#         for one in row:
#             if one is not None:
#                 r.append(one)
#             else:
#                 r.append(None)
#         data.append(r)
#     samples = []
#     at = 
#     while True:
#         sample = {"items": [], "conc": []}
#         try:
#             sampleid = data[at + 1][0]
#             sample["sampleid"] = sampleid

#             logging.info(sampleid)
#         except IndexError:
#             break
#         col = 1
#         while True:
#             try:
#                 # logging.info(at+2,col)
#                 item = data[at + 2][col]
#                 conc = data[at + 3][col]
#                 # should replace blank to other char
#                 sample["items"].append((item, ""))  # .append(item + "_")
#                 sample["conc"].append(conc)
#                 sample["mass"] = ""
#                 sample["volume"] = ""
#             except IndexError:
#                 break
#             if (item == ""):
#                 break
#             col += 1
#         if not create_table:
#             self.cTable(sample)
#             create_table = True
#         self.iTable(sample)
#         if sampleid == "":
#             break
#         samples.append(sample)
#         at += 7
#     logging.info(samples)
#     logging.info(len(samples))